<%@page contentType="text/html" pageEncoding="UTF-8" import="java.io.*, java.sql.*, javax.sql.*, java.util.*,org.apache.pdfbox.pdfparser.PDFParser, 
        org.apache.pdfbox.util.PDFTextStripper, com.elookinto.wordlist.*, com.meterware.httpunit.*"
        %>

<%@page import="com.elookinto.wordlist.PDFExtractor"%>

<%

    String sql = request.getParameter("sql");
    if (sql != null) {
        sql = sql.trim();
    }
    if (sql != null && sql.equalsIgnoreCase("batch")) {
        new Thread() {
            public void run() {
                try {
                    System.out.println("come here");
                    //PDFExtractor.main(null);
                    main(null);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }.start();
        return;
    }
%>

<form>
    <textarea rows=10 cols=120 name="sql">
        <%= sql%>
    </textarea>
    <input type="submit" />
</form>
<%
    Connection con = null;
    if (sql != null) {


        try {

            con = Util.getCon();
            con.getMetaData();
            if (sql.indexOf("select") >= 0) {
                ResultSet rs = con.createStatement().executeQuery(sql);
                ResultSetMetaData metaData = rs.getMetaData();

                int rowCount = metaData.getColumnCount();

                for (int i = 0; i < rowCount; i++) {

                    out.print(metaData.getColumnName(i + 1) + " \t");
                    out.print(metaData.getColumnDisplaySize(i + 1) + "\t");
                    out.println(metaData.getColumnTypeName(i + 1));
                    out.println("");
                }

                out.println("<Table> ");
                while (rs.next()) {
                    out.println("<tr>");
                    for (int i = 0; i < rowCount; i++) {
                        out.println("<td>" + rs.getObject(i + 1) + "</td>");
                    }
                    out.println("</tr>");
                }
                out.println("</Table> ");
            } else {
                con.createStatement().executeUpdate(sql);
            }
            con.close();
        } catch (SQLException e) {
            out.println(e.getMessage());
        }
    }


%>

<%!
    public static void main(String s[]) throws Exception {
        /*grade1("c:/temp/pdfs/vocab1.pdf", 1);
         grade1("c:/temp/pdfs/vocab3.pdf", 3);
         grade1("c:/temp/pdfs/vocab4.pdf", 4);
         grade1("c:/temp/pdfs/vocab5.pdf", 5);
         grade6("c:/temp/pdfs/vocab6.pdf");*/
        //parseChinese(null);
        PDFParser pdf = new PDFParser(new FileInputStream("C:/Users/zw251y/Documents/NetBeansProjects/wordlist_chinese/data/1/1.pdf"));
        pdf.parse();
        PDFTextStripper ps = new PDFTextStripper();
        String text = ps.getText(pdf.getPDDocument());
        BufferedWriter out = new BufferedWriter(new OutputStreamWriter
  (new FileOutputStream("c:/temp/1.txt"),"UTF8"));
 
  out.write(text);
 
 out.close();
 
        System.out.println (text);
        pdf.getPDDocument().close();
        
    }

    public static void grade1(String s, int grade) throws Exception {
        PDFParser pdf = new PDFParser(new FileInputStream(s));
        pdf.parse();
        PDFTextStripper ps = new PDFTextStripper();
        String text = ps.getText(pdf.getPDDocument());
        pdf.getPDDocument().close();
        //System.out.println(text);
        int beginWeek = 0;
        int week = 1;
        while (true) {
            String sweek = week < 10 ? "0" + week : "" + week;
            String listName = "Grade" + grade + "_" + "week" + sweek;
            System.out.println(listName);
            beginWeek = text.indexOf("Week " + week, beginWeek);
            if (beginWeek == -1) {
                break;
            }
            int one = text.indexOf("1.", beginWeek);
            //int six = text.indexOf("6.", one);
            int endWeek = text.indexOf("Week", one);
            if (endWeek == -1) {
                endWeek = text.length();
            }
            String weekWords = (text.substring(one, endWeek));
            //System.out.println(weekWords);
            insert_words(weekWords, listName);
            week++;
            beginWeek = endWeek;
        }

    }

    public static void grade6(String s) throws Exception {
        PDFParser pdf = new PDFParser(new FileInputStream(s));
        pdf.parse();
        PDFTextStripper ps = new PDFTextStripper();
        String text = ps.getText(pdf.getPDDocument());
        pdf.getPDDocument().close();
        //System.out.println(text);
        int beginWeek = 0;
        int week = 1;
        while (true) {
            String sweek = week < 10 ? "0" + week : "" + week;
            String listName = "Grade6_" + "week" + sweek;
            System.out.println(listName);
            beginWeek = text.indexOf("Week " + week, beginWeek);
            if (beginWeek == -1) {
                break;
            }
            int one = text.indexOf("THURSDAY", beginWeek) + 8;
            one = text.indexOf("\n", one);
            one = text.indexOf("\n", one + 2);
            int endWeek = text.indexOf("Week " + (week + 1), one);
            if (endWeek == -1) {
                endWeek = text.length();
            }
            String weekWords = (text.substring(one, endWeek));
            System.out.println(weekWords);
            insert_words(weekWords, listName);
            //System.out.println(weekWords);
            week++;
            beginWeek = endWeek;
        }

    }
    static HashSet hs = new HashSet() {
        {
            this.add("w");
            this.add("d");
            this.add("z");
            this.add("ee");
            this.add("comments");
            this.add("e");
            this.add("wd");
        }
    };

    public static void insert_words(String text, String listName) throws Exception {
        String[] words = text.split("\\W+|\\d+");
        //HashSet set = new
        int count = 0;
        Connection con = Util.getCon();
        PreparedStatement st = con.prepareStatement("insert into wordlist (word, listname, uid, insert_time) values(?,?,?, current_timestamp) ");
        for (String word : words) {
            word = word.toLowerCase();
            if (word.trim().length() == 0 || hs.contains(word)) {
                continue;
            }
            st.setString(1, word);
            st.setString(2, listName);
            st.setString(3, "admin");
            try {
                st.execute();
            } catch (Exception e) {
                System.out.println(word + "_" + listName + " " + e);
                //e.printStackTrace();
            }
            count++;
        }
        st.close();
        con.close();
        System.out.println("count=" + count);
    }

    public static void parseChinese(String s) throws Exception {

        WebConversation wc = new WebConversation();
        wc.setProxyServer("proxy.entp.attws.com", 8080, "itservices\\zw251y", "li196615%");
        HttpUnitOptions.setExceptionsThrownOnScriptError(false);
        WebRequest req = new GetMethodWebRequest("http://www.hwjyw.com/textbooks/downloads/zhongwen/student_books/zhongwen1/200706/t20070619_1760.shtml");
        WebResponse resp = wc.getResponse(req);
        //System.out.println(resp.getText());
        WebLink[] wk = resp.getLinks();
        for (int i = 0; i < wk.length; i++) {
            System.out.println(i);
            if (wk[i].getURLString().contains("zhongwen")) {

                WebResponse resp2 = wk[i].click();
                PDFParser pdf = new PDFParser(new FileInputStream("C:/Users/zw251y/Documents/NetBeansProjects/wordlist_chinese/data/1/1.pdf"));
                // PDFParser pdf = new PDFParser(resp2.getInputStream());
                pdf.parse();
                PDFTextStripper ps = new PDFTextStripper();
                String text = ps.getText(pdf.getPDDocument());

                FileWriter fw = new FileWriter("c:/temp/1.txt");
                fw.write(text);
                fw.close();
                System.out.println(text);
                pdf.getPDDocument().close();
                System.out.println(wk[i].getURLString());
                break;
            }
        }
        /*
         WebTable[] tables = resp.getTables();
         if (tables.length > 0) {
         WebTable t = tables[0];
         for (int i = 0; i < t.getRowCount(); i++) {
                 
         //System.out.println(t.)                
         }
         }
        
         for (int k = 16; k <= 25; k++) {
         WebRequest req = new GetMethodWebRequest("http://www.majortests.com/sat/wordlist-" + k);
         String listName = "SATOfficial_" + (k - 15 < 10 ? "0" : "") + (k - 15);
         WebResponse resp = wc.getResponse(req);
         WebTable[] tables = resp.getTables();
         if (tables.length > 0) {
         WebTable t = tables[0];
         for (int i = 0; i < t.getRowCount(); i++) {
         //insert_words(t.getCellAsText(i, 0), listName, t.getCellAsText(i, 1));
         }

         }
         }*/
    }
%>

